{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "316f4333",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "# 使用上下文管理器自动处理连接和事务提交/回滚\n",
    "with sqlite3.connect('example.db') as conn:\n",
    "    cursor = conn.cursor()\n",
    "\n",
    "    # 创建表\n",
    "    cursor.execute(\"\"\"\n",
    "        CREATE TABLE IF NOT EXISTS users (\n",
    "            id INTEGER PRIMARY KEY,\n",
    "            name TEXT\n",
    "        )\n",
    "    \"\"\")\n",
    "\n",
    "    # 插入数据（推荐使用参数化方式）\n",
    "    names = [('Alice',), ('Bob',), ('Charlie',), ('David',), ('Eve',)]\n",
    "    cursor.executemany(\"INSERT INTO users (name) VALUES (?)\", names)\n",
    "\n",
    "    # 执行查询并获取所有数据\n",
    "    cursor.execute(\"SELECT * FROM users\")\n",
    "    rows = cursor.fetchall()\n",
    "\n",
    "    # 可选：打印数据验证是否写入成功\n",
    "    print(\"当前数据库中的用户：\")\n",
    "    for row in rows:\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12f78af9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "with sqlite3.connect('example.db') as conn:\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(\"SELECT * FROM users\")\n",
    "    # cursor.fetchall()\n",
    "    for row in cursor.fetchall():\n",
    "        print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c10369ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "size = 10\n",
    "idx = 1\n",
    "db_path = r\"c:\\火车采集器V10.27\\Data\\233\\SpiderResult.db3\"\n",
    "table = \"Content\"\n",
    "with sqlite3.connect(db_path) as conn:\n",
    "    cursor = conn.cursor()\n",
    "\n",
    "    # 每次读取前重新执行查询，确保游标位于结果集开头\n",
    "    # cursor.execute()方法返回的是游标对象(执行后，cursor 对象会包含查询的结果集)\n",
    "    cursor.execute(f\"SELECT * FROM {table}\")\n",
    "\n",
    "    while True:\n",
    "        rows = cursor.fetchmany(size)\n",
    "        if not rows:\n",
    "            print(\"所有数据已读取完毕。\")\n",
    "            break\n",
    "\n",
    "        print(f\"读取第 {idx} 批次数据:\")\n",
    "        print(f\"Batch : {idx}\")\n",
    "        for row in rows:\n",
    "            print(row)\n",
    "        idx += 1\n",
    "cursor.close()\n",
    "conn.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d543cb68",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import time\n",
    "\n",
    "size = 10\n",
    "idx = 1\n",
    "db_path = r\"c:\\火车采集器V10.27\\Data\\250\\SpiderResult.db3\"\n",
    "table = \"Content\"\n",
    "with sqlite3.connect(db_path) as conn:\n",
    "    cursor = conn.cursor()\n",
    "\n",
    "    # 每次读取前重新执行查询，确保游标位于结果集开头\n",
    "    # cursor.execute()方法返回的是游标对象(执行后，cursor 对象会包含查询的结果集)\n",
    "    cursor.execute(f\"SELECT * FROM {table}\")\n",
    "\n",
    "    while True:\n",
    "        rows = cursor.fetchmany(size)\n",
    "        if not rows:\n",
    "            print(\"所有数据已读取完毕。\")\n",
    "            break\n",
    "\n",
    "        print(f\"读取第 {idx} 批次数据:\")\n",
    "        print(f\"Batch : {idx}\")\n",
    "        for row in rows:\n",
    "            print(row)\n",
    "        idx += 1\n",
    "        time.sleep(1)\n",
    "\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd93d52d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 方案：采用limit+offset的方式分批查询\n",
    "import sqlite3\n",
    "import time\n",
    "\n",
    "db=r'c:\\火车采集器V10.27\\Data\\248\\SpiderResult.db3'\n",
    "table='Content'\n",
    "conn = sqlite3.connect(db)\n",
    "cursor = conn.cursor()\n",
    "\n",
    "batch_size = 100  # 每次读取的记录数\n",
    "offset = 0\n",
    "\n",
    "while True:\n",
    "    query = f\"SELECT * FROM {table} LIMIT {batch_size} OFFSET {offset}\"\n",
    "    cursor.execute(query)\n",
    "    rows = cursor.fetchall()\n",
    "    \n",
    "    if not rows:\n",
    "        break  # 所有数据已读完\n",
    "    \n",
    "    # 处理当前 batch 的数据\n",
    "    for row in rows:\n",
    "        # 处理每一行\n",
    "        # pass\n",
    "        print(row)\n",
    "    \n",
    "    offset += batch_size\n",
    "    time.sleep(1)\n",
    "\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16ebba38",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
